package com.tripspins.app.helper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {

    private static final String DROP_TRIPS = "drop table trips;";
    private static final String DROP_pins = "drop table pins;";
    private static final String DROP_attachments = "drop table attachments;";
    private static final String DROP_attachment_type = "drop table attachment_type;";
    private static final String DROP_ADDRESS = "drop table pin_address;";
    private static final String DROP_WEATHER = "drop table pin_weather;";
    private static final String DROP_CONTACTS = "drop table contacts;";
    private static final String DROP_USERS = "drop table users;";
    private static final String DROP_LOGIN = "drop table login;";


    private static final String CREATE_TABLE_CONTACT = "create table contacts " +
            "( _id                integer   primary key" +
            ", txt_synced TEXT NOT NULL default 'N'" +
            ", id_attachment      integer" +
            ", txt_source         integer" +
            ", txt_id_on_source   integer" +
            ", txt_name           text" +
            ", txt_picture_path   text" +
            ", txt_thumbnail_path text" +
            ", txt_phone_number   text);";

    private static final String CREATE_TABLE_ATTACHMENT_TYPE = "create table attachment_type " +
            "( _id              integer primary key" +
            " ,txt_description  text);";


    private static final String CREATE_TABLE_ADDRESS = "create table pin_address " +
            "( _id integer primary key" +
            ",txt_synced TEXT NOT NULL default 'N'" +
            ", id_pin      integer" +
            ", txt_country text" +
            ", txt_state   text" +
            ", txt_city    text" +
            ", txt_street  text" +
            ", num_number  integer" +
            ", txt_locale);";

    private static final String CREATE_TABLE_WEATHER = "create table pin_weather " +
            " (_id                   integer primary key " +
            " ,id_pin                INTEGER NOT NULL" +
            ",txt_synced TEXT NOT NULL default 'N'" +
            " ,num_cloud_cover       REAL" +
            " ,num_humidity          REAL" +
            " ,num_precipitation_mm  REAL" +
            " ,num_pressure          REAL" +
            " ,num_temp_c            REAL" +
            " ,num_temp_f            REAL" +
            " ,num_visibility        REAL" +
            " ,txt_wind_dir_16Point  TEXT" +
            " ,txt_wind_dir_degree   TEXT" +
            " ,num_wind_speed_kmph   REAL" +
            " ,num_wind_speed_mph    REAL" +
            " ,txt_description       TEXT" +
            " ,txt_icon_url          TEXT);";

    private static final String CREATE_TABLE_ATTACHMENTS = "CREATE TABLE Attachments "+
            " (_id                 INTEGER primary key"+
            " ,id_pin              INTEGER "+
            ", txt_synced_data     text NOT NULL default 'N'" +
            ", txt_synced_file     text NOT NULL default 'N'" +
            " ,txt_file_path       TEXT"+
            " ,txt_thumbnail_path  TEXT"+
            " ,id_attachment_type  INTEGER);";


    private static final String CREATE_TABLE_PINS = "CREATE TABLE Pins"+
            " (_id              INTEGER primary key"+
            " ,id_trip          INTEGER NOT NULL"+
            " ,txt_synced       TEXT NOT NULL default 'N'" +
            " ,dt_pin           TEXT"+
            " ,num_latitude     REAL"+
            " ,txt_title        TEXT"+
            " ,txt_note         TEXT"+
            " ,num_longitude    REAL);";

    private static final String CREATE_TABLE_TRIPS = "CREATE TABLE Trips "+
            " (_id        INTEGER primary key"+
            ", id_on_server       integer" +
            " ,id_user    INTEGER NOT NULL " +
            " ,txt_title  TEXT NOT NULL " +
            " ,txt_status TEXT NOT NULL " + // (P)lanned - (R)unning - (F)inished
            " ,txt_synced TEXT NOT NULL default 'N' );";

    private static final String CREATE_TABLE_USERS = "CREATE TABLE Users "+
            " (_id        INTEGER primary key"+
            ", id_on_server       integer" +
            " ,txt_synced TEXT NOT NULL default 'N'" +
            " ,txt_fb_id  TEXT NOT NULL " +
            " ,txt_name   TEXT NOT NULL " +
            " , txt_token TEXT );";

    private static final String CREATE_TABLE_LOGIN = "CREATE TABLE Login "+
            " ( id_user integer );";
    private static final String POPULATE_TABLE_LOGIN =
            "insert into login values (null);";
    private static final String UPGRADE_7_to_8  =
            DROP_CONTACTS
                    + DROP_attachments
                    + DROP_attachment_type
                    + DROP_ADDRESS
                    + DROP_WEATHER
                    + DROP_pins
                    +DROP_TRIPS
                    + DROP_USERS ;


    private static final String UNSYNC_1 = "update trips set txt_synced = 'N'; ";
    private static final String UNSYNC_2 = "update pins set txt_synced = 'N'; " ;
    private static final String UNSYNC_3 = "update attachments set txt_synced_data = 'N' , txt_synced_file = 'N'; " ;
    private static final String UNSYNC_4 = "update pin_weather set txt_synced = 'N'; " ;
    private static final String UNSYNC_5 = "update pin_address set txt_synced = 'N'; " ;
    private static final String ALTER_USER = "ALTER TABLE USERS ADD TXT_TOKEN TEXT " ;

    private static final String DATABASE_NAME = "tripsandpins.db";
    private static final String DATABASE_NAME_DEBUG = "/mnt/sdcard/tripsandpins.db";
    private static final int DATABASE_VERSION = 1;


    public DbHelper(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_ATTACHMENT_TYPE);
        db.execSQL(CREATE_TABLE_CONTACT);
        db.execSQL(CREATE_TABLE_ADDRESS);
        db.execSQL(CREATE_TABLE_WEATHER);
        db.execSQL(CREATE_TABLE_ATTACHMENTS);
        db.execSQL(CREATE_TABLE_PINS);
        db.execSQL(CREATE_TABLE_TRIPS);
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_LOGIN);
        db.execSQL(POPULATE_TABLE_LOGIN);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(UNSYNC_1);
        db.execSQL(UNSYNC_2);
        db.execSQL(UNSYNC_3);
        db.execSQL(UNSYNC_4);
        db.execSQL(UNSYNC_5);
        db.execSQL(ALTER_USER);
//        onCreate(db);

    }

}
